/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package databaseupgrade;

import TimeRecording.Upgrade.DatabaseCredentialsType;
import TimeRecording.Upgrade.SqlCommandStatusType;
import TimeRecording.Upgrade.SqlCommandType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author david
 */
public class Database {

    private final String GET_CURRENT_DB_VERSION =
            "SELECT database_version FROM control";

    private final String SET_CURRENT_DB_VERSION =
            "UPDATE control SET database_version  = ?";

    private Connection connection;

    public Database(DatabaseCredentialsType credentials) {

        try {
            Class.forName(credentials.getDriver());
            
            connection = (Connection) java.sql.DriverManager.getConnection(
                credentials.getUrl(),
                credentials.getUsername(),
                credentials.getPassword());
            
            connection.setAutoCommit(false);

        } catch (Throwable ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void tearDown() {
        safeClose(connection);
    }

    public boolean execute(SqlCommandType command) {

        Statement s = null;

        try {
            s = connection.createStatement();
            int rowCount = s.executeUpdate(command.getCommand());

            command.setResponse(rowCount + " rows updated/inserted");
            command.setStatus(SqlCommandStatusType.SUCCEEDED);
            return true;

        } catch (Throwable ex) {
            
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            command.setResponse(ex.getMessage());
            command.setStatus(SqlCommandStatusType.FAILED);
            return false;

        } finally {
            safeClose(s);
        }
    }

    public String getDatabaseVersion() {

        Statement s = null;
        ResultSet rs = null;

        try {
            s = connection.createStatement();
            rs = s.executeQuery(GET_CURRENT_DB_VERSION);
            if (rs.next()) {
                return Integer.toString(rs.getInt("database_version"));
            } else {
                return "unknown";
            }

        } catch (Throwable ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
            return "unknown";

        } finally {
            safeClose(rs);
            safeClose(s);
        }
    }

    public void setDatabaseVersion(int version) {

        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement(SET_CURRENT_DB_VERSION);
            ps.setInt(1, version);
            ps.executeUpdate();

        } catch (Throwable ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);

        } finally {
            safeClose(ps);
        }
    }

    public void commit() {
        try {
            connection.commit();
        } catch (Throwable ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void rollback() {
        try {
            connection.rollback();
        } catch (Throwable ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Simply closes the supplied result set and logs any errors reported.  Any
     * exception encountered are logged and swallowed.
     *
     * @param rs the result set to be closed
     */
    private void safeClose(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Throwable ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    /**
     * Simply closes the supplied statement and logs any errors reported.  Any
     * exception encountered are logged and swallowed.
     *
     * @param s the statement to be closed
     */
    private void safeClose(Statement s) {
        try {
            if (s != null) {
                s.close();
            }
        } catch (Throwable ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Simply closes the supplied statement and logs any errors reported.  Any
     * exception encountered are logged and swallowed.
     *
     * @param s the statement to be closed
     */
    private void safeClose(Connection c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (Throwable ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
